﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;

/// <summary>
/// Dbcom 的摘要说明
/// </summary>
public class Dbcom
{
    private static string _connectionString;  
    private static OracleConnection cnn;

    /// <summary>
    /// 数据字典
    /// </summary>
    public static DataSet DS_CODE;
    /// <summary>
    /// 行政区域字典
    /// </summary>
    public static DataSet DS_AREA;


    public Dbcom()
    {
        ConnectionString = ConfigurationManager.AppSettings["DBLink"];

        if (DS_CODE == null ||
           DS_AREA == null)
        {
            RefBaseData();
        }
       
    }

    /// <summary>
    /// 返回服务器时间
    /// </summary>
    /// <returns></returns>
    public static DateTime GetSystemTime()
    {
        return DateTime.Now;
    }

    /// <summary>
    /// 刷新基础数据
    /// </summary>
    public static void RefBaseData()
    {          
        DS_AREA = GetDataSet("select area_code,area_name from area_dict");
        DS_CODE = GetDataSet("select * from t_data_code where type in (53,70,71,134,132,130)");
    }

    public static string ConnectionString
    {
        get
        {
            return _connectionString;
        }

        set
        {
            _connectionString = value;
        }
    }

    /// <summary>
    /// 打开数据库连接
    /// </summary>
    private static void Open()
    {
        cnn = new OracleConnection(ConnectionString);
        cnn.Open();
    }

    /// <summary>
    /// 关闭数据库连接
    /// </summary>
    private static void Close()
    {
        cnn.Close();
    }


    /// <summary>
    /// 返回DataSet
    /// </summary>
    /// <param name=CmdString></param>
    /// <param name=TableName></param>
    /// <returns></returns>  
    public static DataSet GetDataSet(string CmdString)
    {
        try
        {
            Open();
            OracleDataAdapter myDa = new OracleDataAdapter();
            myDa.SelectCommand = new OracleCommand(CmdString, cnn);
            DataSet myDs = new DataSet();
            myDa.Fill(myDs, "table");
            Close();
            return myDs;
        }
        catch
        {
            return null;
        }
        finally
        {
            Close();
        }
    }

    /// <summary>
    /// 返回DataSet 多张表
    /// </summary>
    /// <param name=CmdString></param>
    /// <param name=TableName></param>
    /// <returns></returns>       
    public static DataSet GetDataSets(Class_Table[] tabsqls)
    {
        OracleConnection cnn = new OracleConnection(ConnectionString);
        OracleDataAdapter myDa = new OracleDataAdapter();
        DataSet myDs = new DataSet();
        try
        {
            cnn.Open();
            for (int i = 0; i < tabsqls.Length; i++)
            {
                myDa.SelectCommand = new OracleCommand(tabsqls[i].Sql, cnn);
                myDa.Fill(myDs, tabsqls[i].Tablename);
            }
            return myDs;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            cnn.Close();
        }
    }

    /// <summary>
    /// 根据行号和列名返回值
    /// </summary>
    /// <param name="CmdString">SQl语句</param>
    /// <param name="rowindex">行号</param>
    /// <param name="colName">列名</param>
    /// <returns></returns>
    public static string ReadSqlVal(string CmdString, int rowindex, string colName)
    {
        OracleConnection cnn = new OracleConnection(ConnectionString);
        try
        {

            cnn.Open();
            OracleDataAdapter myDa = new OracleDataAdapter();
            myDa.SelectCommand = new OracleCommand(CmdString, cnn);
            DataSet myDs = new DataSet();
            myDa.Fill(myDs, "table");
            return myDs.Tables[0].Rows[rowindex][colName].ToString();
        }
        catch
        {
            return null;
        }
        finally
        {
            cnn.Close();
        }
    }


    /// <summary>
    /// 返回影响数据库的行数
    /// </summary>
    /// <param name=CmdString></param>
    /// <returns></returns>
    public static int ExecuteSQL(string CmdString)
    {
        OracleConnection cnn = new OracleConnection(ConnectionString);
        try
        {
            cnn.Open();
            OracleCommand myCmd = new OracleCommand(CmdString, cnn);
            int Cmd = myCmd.ExecuteNonQuery();
            return Cmd;
        }
        catch(Exception ex)
        {
            throw ex;
        }
        finally
        {
            cnn.Close();
        }
    }

    ///   <summary> 
    ///   批量执行Sql语句 
    ///   </summary> 
    ///   <param name= "BatchSql "> Sql语句数组 </param> 
    public static int ExecuteBatch(string[] BatchSql)
    {
        //打开连接 
        OracleConnection cnn = new OracleConnection(ConnectionString);
        cnn.Open();

        //创建事务 
        OracleCommand cmd = cnn.CreateCommand();
        //OracleTransaction transaction = cnn.BeginTransaction(IsolationLevel.ReadCommitted);
        OracleTransaction transaction = cnn.BeginTransaction();

        cmd.Transaction = transaction;
        int y = 0;
        try
        { 
            for (int i = 0; i < BatchSql.Length; i++)
            {
                if (BatchSql[i].Trim() != "")
                {
                    cmd.CommandText = BatchSql[i];
                    cmd.ExecuteNonQuery();
                }
            }
            y = y + 1;
            //执行完成后提交事务 
            transaction.Commit();
            return y;
        }
        catch (Exception ex)
        {
            //回滚事务 
            transaction.Rollback();
            throw ex;

        }
        finally
        {
            //关闭连接 
            cnn.Close();
        }
    }

    /// <summary>
    /// 自动生成ID
    /// </summary>
    /// <param name="tablename">表名</param>
    /// <param name="Idname">id字段名</param>
    /// <returns></returns>
    public static int GenId()
    {
        try
        {
            string val = ReadSqlVal("select T_GENID.NEXTVAL col from dual WHERE ROWNUM =1", 0, "col");
            if (val == null)
                return 1;
            return Convert.ToInt32(val);
        }
        catch
        {
            return 1;
        }
    }

    /// <summary>
    /// 以带参数的形式执行操作
    /// </summary>
    /// <param name="CmdString">SQL语句</param>
    /// <param name="Parameters">参数集合</param>       
    /// <returns></returns>
    public static int ExecuteSQLWithParams(string CmdString, DBParameter[] paremts)
    {
        OracleConnection cnn = new OracleConnection(ConnectionString);
        try
        {

            cnn.Open();
            OracleCommand myCmd = new OracleCommand(CmdString, cnn);
            for (int i = 0; i < paremts.Length; i++)
            {
                OracleParameter Parameter = new OracleParameter();
                Parameter.Value = paremts[i].Value;
                Parameter.ParameterName = paremts[i].ParameterName;
                Parameter.OracleDbType = paremts[i].DBType;
                Parameter.Size = paremts[i].Size;
                myCmd.Parameters.Add(Parameter);
            }
            int Cmd = myCmd.ExecuteNonQuery();

            return Cmd;
        }
        catch(Exception ex)
        {
            throw ex;
        }
        finally
        {
            cnn.Close();
        }
    }

    /// <summary>
    /// 连接测试
    /// </summary>
    /// <returns></returns>
    public static bool ConnectTest()
    {
        try
        {
            OracleConnection cnn = new OracleConnection(ConnectionString);
            cnn.Open();
            cnn.Close();
            return true;
        }
        catch
        {
            return false;
        }
    }

    /// <summary>
    /// 判断是否是数值类型
    /// </summary>
    /// <param name="str">需要判断的参数</param>
    /// <returns></returns>
    public static bool IsNumeric(string str)
    {
        try
        {
            double.Parse(str);
            return true;
        }
        catch
        {
            return false;
        }
    }

}